from flask import Blueprint, jsonify, request
from src.models import MajorPlan, Major, Course, db
from src.models.db import get_db_cursor
import logging

# 配置日志
logger = logging.getLogger(__name__)

major_plan_bp = Blueprint('major_plan', __name__)

@major_plan_bp.route('/majors/<string:major_id>/grade/<int:grade>', methods=['GET'])
def get_major_courses(major_id, grade):
    try:
        with get_db_cursor() as cursor:
            cursor.execute("""
                WITH course_ratings AS (
                    SELECT 
                        cr."课程编号",
                        cr."教师编号",
                        cr."得分高低（1到10）",
                        cr."水课程度（1到10）",
                        COUNT(*) as rating_count
                    FROM "课程评价表" cr
                    GROUP BY cr."课程编号", cr."教师编号", cr."得分高低（1到10）", cr."水课程度（1到10）"
                )
                SELECT 
                    "课程表"."课程编号",
                    "课程表"."课程名称",
                    "课程表"."学分",
                    "教师表"."教师编号",
                    "教师表"."教师姓名",
                    "授课表"."教室",
                    "授课表"."授课编号",
                    cr."得分高低（1到10）" as "得分高低",
                    cr."水课程度（1到10）" as "水课程度",
                    cr.rating_count as "评价数量"
                FROM "专业培养方案表"
                JOIN "课程表" ON "专业培养方案表"."课程编号" = "课程表"."课程编号"
                LEFT JOIN "授课表" ON "课程表"."课程编号" = "授课表"."课程编号"
                LEFT JOIN "教师表" ON "授课表"."教师编号" = "教师表"."教师编号"
                LEFT JOIN course_ratings cr ON "课程表"."课程编号" = cr."课程编号" 
                    AND "教师表"."教师编号" = cr."教师编号"
                WHERE "专业培养方案表"."专业编号" = %s AND "专业培养方案表"."年级" = %s
                ORDER BY "课程表"."课程编号", "授课表"."授课编号"
            """, (major_id, grade))
            
            courses = cursor.fetchall()
            
            # 处理查询结果
            result = []
            
            for course in courses:
                if course[3]:  # 如果有教师信息
                    course_dict = {
                        '课程编号': f"{course[0]}_{course[3]}", # 组合课程编号和教师编号
                        '原始课程编号': course[0],
                        '课程名称': f"{course[1]} - {course[4]}班", # 课程名称加上教师名
                        '学分': float(course[2]) if course[2] else None,
                        '教师编号': course[3],
                        '教师姓名': course[4],
                        '教室': course[5],
                        '授课编号': course[6],
                        '得分高低': float(course[7]) if course[7] else None,
                        '水课程度': float(course[8]) if course[8] else None,
                        '评价数量': course[9] if course[9] else 0
                    }
                    result.append(course_dict)
                
            return jsonify({
                'status': 'success',
                'data': result
            })
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500

@major_plan_bp.route('/majors/<string:major_id>/plans', methods=['GET'])
def get_major_plans(major_id):
    """获取指定专业的培养方案"""
    major = Major.query.get_or_404(major_id)
    
    # 按年级分组获取课程
    plans_by_grade = {}
    for plan in major.培养方案:
        grade = plan.年级
        if grade not in plans_by_grade:
            plans_by_grade[grade] = []
            
        course = plan.course
        plans_by_grade[grade].append({
            '课程编号': course.课程编号,
            '课程名称': course.课程名称,
            '学分': float(course.学分)
        })
    
    # 转换为列表格式
    plans = [{
        '年级': grade,
        '课程列表': courses
    } for grade, courses in sorted(plans_by_grade.items())]
    
    return jsonify({
        'status': 'success',
        'data': {
            '专业编号': major.专业编号,
            '专业名称': major.专业名称,
            '培养方案': plans
        }
    })

@major_plan_bp.route('/majors/<string:major_id>/plans', methods=['POST'])
def add_course_to_plan(major_id):
    """向培养方案添加课程"""
    major = Major.query.get_or_404(major_id)
    data = request.get_json()
    
    # 验证数据
    if not data or not data.get('课程编号') or '年级' not in data:
        return jsonify({
            'status': 'error',
            'message': '缺少必要的培养方案信息'
        }), 400
        
    # 检查课程是否存在
    course = Course.query.get(data['课程编号'])
    if not course:
        return jsonify({
            'status': 'error',
            'message': '课程不存在'
        }), 404
        
    # 检查是否已存在该记录
    if MajorPlan.query.filter_by(
        专业编号=major_id,
        年级=data['年级'],
        课程编号=data['课程编号']
    ).first():
        return jsonify({
            'status': 'error',
            'message': '该课程已在培养方案中'
        }), 400
        
    # 创建培养方案记录
    plan = MajorPlan(
        专业编号=major_id,
        年级=data['年级'],
        课程编号=data['课程编号']
    )
    
    try:
        db.session.add(plan)
        db.session.commit()
        return jsonify({
            'status': 'success',
            'message': '课程添加成功',
            'data': {
                '方案编号': plan.方案编号,
                '专业编号': plan.专业编号,
                '年级': plan.年级,
                '课程编号': plan.课程编号,
                '课程名称': course.课程名称
            }
        }), 201
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'status': 'error',
            'message': f'课程添加失败：{str(e)}'
        }), 500

@major_plan_bp.route('/plans/<int:plan_id>', methods=['DELETE'])
def remove_course_from_plan(plan_id):
    """从培养方案中移除课程"""
    plan = MajorPlan.query.get_or_404(plan_id)
    
    try:
        db.session.delete(plan)
        db.session.commit()
        return jsonify({
            'status': 'success',
            'message': '课程移除成功'
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'status': 'error',
            'message': f'课程移除失败：{str(e)}'
        }), 500

@major_plan_bp.route('/majors/<string:major_id>/plans/batch', methods=['POST'])
def batch_add_courses(major_id):
    """批量添加课程到培养方案"""
    major = Major.query.get_or_404(major_id)
    data = request.get_json()
    
    if not data or not isinstance(data.get('课程列表'), list):
        return jsonify({
            'status': 'error',
            'message': '缺少课程列表数据'
        }), 400
        
    results = []
    for course_data in data['课程列表']:
        # 验证每条数据
        if not course_data.get('课程编号') or '年级' not in course_data:
            continue
            
        # 检查课程是否存在
        course = Course.query.get(course_data['课程编号'])
        if not course:
            continue
            
        # 检查是否已存在该记录
        if MajorPlan.query.filter_by(
            专业编号=major_id,
            年级=course_data['年级'],
            课程编号=course_data['课程编号']
        ).first():
            continue
            
        # 创建培养方案记录
        plan = MajorPlan(
            专业编号=major_id,
            年级=course_data['年级'],
            课程编号=course_data['课程编号']
        )
        
        db.session.add(plan)
        results.append({
            '课程编号': course.课程编号,
            '课程名称': course.课程名称,
            '年级': course_data['年级']
        })
    
    try:
        db.session.commit()
        return jsonify({
            'status': 'success',
            'message': f'成功添加{len(results)}门课程',
            'data': results
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'status': 'error',
            'message': f'批量添加失败：{str(e)}'
        }), 500 